--    Program: 		CAPPO_RECEIPT_HEADER.sql
--    Description:  Import RCV Shipment Headers information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         23-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);

   l_shipment_header_id     number;
                    		                 		
   cursor cur_rcv_shipment_headers is
      select * from CRST_RCV_SHIPMENT_HEADERS for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_rcv_shipment_headers loop   	
     l_err_msg_tmp := '';
     
     select RCV_SHIPMENT_HEADERS_S.NEXTVAL
     into l_shipment_header_id
     FROM DUAL;
     
     update CRST_RCV_SHIPMENT_HEADERS
     set    shipment_header_id_n = l_shipment_header_id
     where  shipment_header_id = cur.SHIPMENT_HEADER_ID;
     
     begin
       -- import data
       insert into RCV_SHIPMENT_HEADERS 
       (
            SHIPMENT_HEADER_ID        ,
            LAST_UPDATE_DATE          ,
            LAST_UPDATED_BY           ,
            CREATION_DATE             ,
            CREATED_BY                ,
            LAST_UPDATE_LOGIN         ,
            RECEIPT_SOURCE_CODE       ,
            VENDOR_ID                 ,
            VENDOR_SITE_ID            ,
            ORGANIZATION_ID           ,
            SHIPMENT_NUM              ,
            RECEIPT_NUM               ,
            SHIP_TO_LOCATION_ID       ,
            BILL_OF_LADING            ,
            PACKING_SLIP              ,
            SHIPPED_DATE              ,
            FREIGHT_CARRIER_CODE      ,
            EXPECTED_RECEIPT_DATE     ,
            EMPLOYEE_ID               ,
            NUM_OF_CONTAINERS         ,
            WAYBILL_AIRBILL_NUM       ,
            COMMENTS                  ,
            ATTRIBUTE_CATEGORY        ,
            ATTRIBUTE1                ,
            ATTRIBUTE2                ,
            ATTRIBUTE3                ,
            ATTRIBUTE4                ,
            ATTRIBUTE5                ,
            ATTRIBUTE6                ,
            ATTRIBUTE7                ,
            ATTRIBUTE8                ,
            ATTRIBUTE9                ,
            ATTRIBUTE10               ,
            ATTRIBUTE11               ,
            ATTRIBUTE12               ,
            ATTRIBUTE13               ,
            ATTRIBUTE14               ,
            ATTRIBUTE15               ,
            USSGL_TRANSACTION_CODE    ,
            GOVERNMENT_CONTEXT        ,
            REQUEST_ID                ,
            PROGRAM_APPLICATION_ID    ,
            PROGRAM_ID                ,
            PROGRAM_UPDATE_DATE       ,
            ASN_TYPE                  ,
            EDI_CONTROL_NUM           ,
            NOTICE_CREATION_DATE      ,
            GROSS_WEIGHT              ,
            GROSS_WEIGHT_UOM_CODE     ,
            NET_WEIGHT                ,
            NET_WEIGHT_UOM_CODE       ,
            TAR_WEIGHT                ,
            TAR_WEIGHT_UOM_CODE       ,
            PACKAGING_CODE            ,
            CARRIER_METHOD            ,
            CARRIER_EQUIPMENT         ,
            CARRIER_EQUIPMENT_NUM     ,
            CARRIER_EQUIPMENT_ALPHA   ,
            SPECIAL_HANDLING_CODE     ,
            HAZARD_CODE               ,
            HAZARD_CLASS              ,
            HAZARD_DESCRIPTION        ,
            FREIGHT_TERMS             ,
            FREIGHT_BILL_NUMBER       ,
            INVOICE_NUM               ,
            INVOICE_DATE              ,
            INVOICE_AMOUNT            ,
            TAX_NAME                  ,
            TAX_AMOUNT                ,
            FREIGHT_AMOUNT            ,
            INVOICE_STATUS_CODE       ,
            ASN_STATUS                ,
            CURRENCY_CODE             ,
            CONVERSION_RATE_TYPE      ,
            CONVERSION_RATE           ,
            CONVERSION_DATE           ,
            PAYMENT_TERMS_ID          ,
            MRC_CONVERSION_RATE_TYPE  ,
            MRC_CONVERSION_DATE       ,
            MRC_CONVERSION_RATE       ,
            SHIP_TO_ORG_ID            ,
            CUSTOMER_ID               ,
            CUSTOMER_SITE_ID          
       )
       values
       (
            l_shipment_header_id          ,
            cur.LAST_UPDATE_DATE          ,
            CUR.LAST_UPDATED_BY,
            cur.CREATION_DATE             ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            cur.LAST_UPDATE_LOGIN         ,
            cur.RECEIPT_SOURCE_CODE       ,
            CAPPOIMP_MAPPING.GetVendorIDMapping(cur.VENDOR_ID),
            CAPPOIMP_MAPPING.GetVendorSiteIDMapping(cur.VENDOR_SITE_ID),
            CAPBRIMP_MAPPING.GetOrganizationMapping(cur.ORGANIZATION_ID),
            cur.SHIPMENT_NUM              ,
            cur.RECEIPT_NUM               ,
            CAPPOIMP_MAPPING.GetHRLocationIDMapping(cur.SHIP_TO_LOCATION_ID), 
            cur.BILL_OF_LADING            ,
            cur.PACKING_SLIP              ,
            cur.SHIPPED_DATE              ,
            cur.FREIGHT_CARRIER_CODE      ,
            cur.EXPECTED_RECEIPT_DATE     ,
            CAPBRIMP_MAPPING.GetEmployeeMapping(cur.EMPLOYEE_ID),
            cur.NUM_OF_CONTAINERS         ,
            cur.WAYBILL_AIRBILL_NUM       ,
            cur.COMMENTS                  ,
            cur.ATTRIBUTE_CATEGORY        ,
            cur.ATTRIBUTE1                ,
            cur.ATTRIBUTE2                ,
            cur.ATTRIBUTE3                ,
            cur.ATTRIBUTE4                ,
            cur.ATTRIBUTE5                ,
            cur.ATTRIBUTE6                ,
            cur.ATTRIBUTE7                ,
            cur.ATTRIBUTE8                ,
            cur.ATTRIBUTE9                ,
            cur.ATTRIBUTE10               ,
            cur.ATTRIBUTE11               ,
            cur.ATTRIBUTE12               ,
            cur.ATTRIBUTE13               ,
            cur.ATTRIBUTE14               ,
            cur.ATTRIBUTE15               ,
            cur.USSGL_TRANSACTION_CODE    ,
            cur.GOVERNMENT_CONTEXT        ,
            cur.REQUEST_ID                ,
            cur.PROGRAM_APPLICATION_ID    ,
            cur.PROGRAM_ID                ,
            cur.PROGRAM_UPDATE_DATE       ,
            cur.ASN_TYPE                  ,
            cur.EDI_CONTROL_NUM           ,
            cur.NOTICE_CREATION_DATE      ,
            cur.GROSS_WEIGHT              ,
            cur.GROSS_WEIGHT_UOM_CODE     ,
            cur.NET_WEIGHT                ,
            cur.NET_WEIGHT_UOM_CODE       ,
            cur.TAR_WEIGHT                ,
            cur.TAR_WEIGHT_UOM_CODE       ,
            cur.PACKAGING_CODE            ,
            cur.CARRIER_METHOD            ,
            cur.CARRIER_EQUIPMENT         ,
            cur.CARRIER_EQUIPMENT_NUM     ,
            cur.CARRIER_EQUIPMENT_ALPHA   ,
            cur.SPECIAL_HANDLING_CODE     ,
            cur.HAZARD_CODE               ,
            cur.HAZARD_CLASS              ,
            cur.HAZARD_DESCRIPTION        ,
            cur.FREIGHT_TERMS             ,
            cur.FREIGHT_BILL_NUMBER       ,
            cur.INVOICE_NUM               ,
            cur.INVOICE_DATE              ,
            cur.INVOICE_AMOUNT            ,
            cur.TAX_NAME                  ,
            cur.TAX_AMOUNT                ,
            cur.FREIGHT_AMOUNT            ,
            cur.INVOICE_STATUS_CODE       ,
            cur.ASN_STATUS                ,
            cur.CURRENCY_CODE             ,
            cur.CONVERSION_RATE_TYPE      ,
            cur.CONVERSION_RATE           ,
            cur.CONVERSION_DATE           ,
            cur.PAYMENT_TERMS_ID          ,
            cur.MRC_CONVERSION_RATE_TYPE  ,
            cur.MRC_CONVERSION_DATE       ,
            cur.MRC_CONVERSION_RATE       ,
            CAPBRIMP_MAPPING.GetOrgMapping(cur.SHIP_TO_ORG_ID),
            cur.CUSTOMER_ID               , --NULL
            cur.CUSTOMER_SITE_ID            --NULL
       );
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPO_RECEIPT_HEADER', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
